JDBC查询 |
您所在的位置：网站首页 › jdbctemplate connection是不是线程变量 › JDBC查询 |
|
前面我们讲了Java程序要通过JDBC接口来查询数据库。JDBC是一套接口规范，它在哪呢？就在Java的标准库java.sql里放着，不过这里面大部分都是接口。接口并不能直接实例化，而是必须实例化对应的实现类，然后通过接口引用这个实例。那么问题来了：JDBC接口的实现类在哪？ 因为JDBC接口并不知道我们要使用哪个数据库，所以，用哪个数据库，我们就去使用哪个数据库的“实现类”，我们把某个数据库实现了JDBC接口的jar包称为JDBC驱动。 因为我们选择了MySQL 5.x作为数据库，所以我们首先得找一个MySQL的JDBC驱动。所谓JDBC驱动，其实就是一个第三方jar包，我们直接添加一个Maven依赖就可以了： mysql mysql-connector-java 5.1.47 runtime注意到这里添加依赖的scope是runtime，因为编译Java程序并不需要MySQL的这个jar包，只有在运行期才需要使用。如果把runtime改成compile，虽然也能正常编译，但是在IDE里写程序的时候，会多出来一大堆类似com.mysql.jdbc.Connection这样的类，非常容易与Java标准库的JDBC接口混淆，所以坚决不要设置为compile。 有了驱动，我们还要确保MySQL在本机正常运行，并且还需要准备一点数据。这里我们用一个脚本创建数据库和表，然后插入一些数据： -- 创建数据库learjdbc: DROP DATABASE IF EXISTS learnjdbc; CREATE DATABASE learnjdbc; -- 创建登录用户learn/口令learnpassword CREATE USER IF NOT EXISTS learn@'%' IDENTIFIED BY 'learnpassword'; GRANT ALL PRIVILEGES ON learnjdbc.* TO learn@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; -- 创建表students: USE learnjdbc; CREATE TABLE students ( id BIGINT AUTO_INCREMENT NOT NULL, name VARCHAR(50) NOT NULL, gender TINYINT(1) NOT NULL, grade INT NOT NULL, score INT NOT NULL, PRIMARY KEY(id) ) Engine=INNODB DEFAULT CHARSET=UTF8; -- 插入初始数据: INSERT INTO students (name, gender, grade, score) VALUES ('小明', 1, 1, 88); INSERT INTO students (name, gender, grade, score) VALUES ('小红', 1, 1, 95); INSERT INTO students (name, gender, grade, score) VALUES ('小军', 0, 1, 93); INSERT INTO students (name, gender, grade, score) VALUES ('小白', 0, 1, 100); INSERT INTO students (name, gender, grade, score) VALUES ('小牛', 1, 2, 96); INSERT INTO students (name, gender, grade, score) VALUES ('小兵', 1, 2, 99); INSERT INTO students (name, gender, grade, score) VALUES ('小强', 0, 2, 86); INSERT INTO students (name, gender, grade, score) VALUES ('小乔', 0, 2, 79); INSERT INTO students (name, gender, grade, score) VALUES ('小青', 1, 3, 85); INSERT INTO students (name, gender, grade, score) VALUES ('小王', 1, 3, 90); INSERT INTO students (name, gender, grade, score) VALUES ('小林', 0, 3, 91); INSERT INTO students (name, gender, grade, score) VALUES ('小贝', 0, 3, 77);在控制台输入mysql -u root -p，输入root口令后以root身份，把上述SQL贴到控制台执行一遍就行。如果你运行的是最新版MySQL 8.x，需要调整一下CREATE USER语句。 JDBC连接使用JDBC时，我们先了解什么是Connection。Connection代表一个JDBC连接，它相当于Java程序到数据库的连接（通常是TCP连接）。打开一个Connection时，需要准备URL、用户名和口令，才能成功连接到数据库。 URL是由数据库厂商指定的格式，例如，MySQL的URL是： jdbc:mysql://:/?key1=value1&key2=value2假设数据库运行在本机localhost，端口使用标准的3306，数据库名称是learnjdbc，那么URL如下： jdbc:mysql://localhost:3306/learnjdbc?useSSL=false&characterEncoding=utf8后面的两个参数表示不使用SSL加密，使用UTF-8作为字符编码（注意MySQL的UTF-8是utf8）。 要获取数据库连接，使用如下代码： // JDBC连接的URL, 不同数据库有不同的格式: String JDBC_URL = "jdbc:mysql://localhost:3306/test"; String JDBC_USER = "root"; String JDBC_PASSWORD = "password"; // 获取连接: Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD); // TODO: 访问数据库... // 关闭连接: conn.close();核心代码是DriverManager提供的静态方法getConnection()。DriverManager会自动扫描classpath，找到所有的JDBC驱动，然后根据我们传入的URL自动挑选一个合适的驱动。 因为JDBC连接是一种昂贵的资源，所以使用后要及时释放。使用try (resource)来自动释放JDBC连接是一个好方法： try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) { ... } JDBC查询获取到JDBC连接后，下一步我们就可以查询数据库了。查询数据库分以下几步： 第一步，通过Connection提供的createStatement()方法创建一个Statement对象，用于执行一个查询； 第二步，执行Statement对象提供的executeQuery("SELECT * FROM students")并传入SQL语句，执行查询并获得返回的结果集，使用ResultSet来引用这个结果集； 第三步，反复调用ResultSet的next()方法并读取每一行结果。 完整查询代码如下： try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) { try (Statement stmt = conn.createStatement()) { try (ResultSet rs = stmt.executeQuery("SELECT id, grade, name, gender FROM students WHERE gender=1")) { while (rs.next()) { long id = rs.getLong(1); // 注意：索引从1开始 long grade = rs.getLong(2); String name = rs.getString(3); int gender = rs.getInt(4); } } } }注意要点： Statement和ResultSet都是需要关闭的资源，因此嵌套使用try (resource)确保及时关闭； rs.next()用于判断是否有下一行记录，如果有，将自动把当前行移动到下一行（一开始获得ResultSet时当前行不是第一行）； ResultSet获取列时，索引从1开始而不是0； 必须根据SELECT的列的对应位置来调用getLong(1)，getString(2)这些方法，否则对应位置的数据类型不对，将报错。 SQL注入使用Statement拼字符串非常容易引发SQL注入的问题，这是因为SQL参数往往是从方法参数传入的。 我们来看一个例子：假设用户登录的验证方法如下： User login(String name, String pass) { ... stmt.executeQuery("SELECT * FROM user WHERE login='" + name + "' AND pass='" + pass + "'"); ... }其中，参数name和pass通常都是Web页面输入后由程序接收到的。 如果用户的输入是程序期待的值，就可以拼出正确的SQL。例如：name = "bob"，pass = "1234"： SELECT * FROM user WHERE login='bob' AND pass='1234'但是，如果用户的输入是一个精心构造的字符串，就可以拼出意想不到的SQL，这个SQL也是正确的，但它查询的条件不是程序设计的意图。例如：name = "bob' OR pass=", pass = " OR pass='"： SELECT * FROM user WHERE login='bob' OR pass=' AND pass=' OR pass=''这个SQL语句执行的时候，根本不用判断口令是否正确，这样一来，登录就形同虚设。 要避免SQL注入攻击，一个办法是针对所有字符串参数进行转义，但是转义很麻烦，而且需要在任何使用SQL的地方增加转义代码。 还有一个办法就是使用PreparedStatement。使用PreparedStatement可以完全避免SQL注入的问题，因为PreparedStatement始终使用?作为占位符，并且把数据连同SQL本身传给数据库，这样可以保证每次传给数据库的SQL语句是相同的，只是占位符的数据不同，还能高效利用数据库本身对查询的缓存。上述登录SQL如果用PreparedStatement可以改写如下： User login(String name, String pass) { ... String sql = "SELECT * FROM user WHERE login=? AND pass=?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setObject(1, name); ps.setObject(2, pass); ... }所以，PreparedStatement比Statement更安全，而且更快。 使用Java对数据库进行操作时，必须使用PreparedStatement，严禁任何通过参数拼字符串的代码！我们把上面使用Statement的代码改为使用PreparedStatement： try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) { try (PreparedStatement ps = conn.prepareStatement("SELECT id, grade, name, gender FROM students WHERE gender=? AND grade=?")) { ps.setObject(1, "M"); // 注意：索引从1开始 ps.setObject(2, 3); try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { long id = rs.getLong("id"); long grade = rs.getLong("grade"); String name = rs.getString("name"); String gender = rs.getString("gender"); } } } }使用PreparedStatement和Statement稍有不同，必须首先调用setObject()设置每个占位符?的值，最后获取的仍然是ResultSet对象。 另外注意到从结果集读取列时，使用String类型的列名比索引要易读，而且不易出错。 注意到JDBC查询的返回值总是ResultSet，即使我们写这样的聚合查询SELECT SUM(score) FROM ...，也需要按结果集读取： ResultSet rs = ... if (rs.next()) { double sum = rs.getDouble(1); } 数据类型有的童鞋可能注意到了，使用JDBC的时候，我们需要在Java数据类型和SQL数据类型之间进行转换。JDBC在java.sql.Types定义了一组常量来表示如何映射SQL数据类型，但是平时我们使用的类型通常也就以下几种： SQL数据类型Java数据类型 BIT, BOOLboolean INTEGERint BIGINTlong REALfloat FLOAT, DOUBLEdouble CHAR, VARCHARString DECIMALBigDecimal DATEjava.sql.Date, LocalDate TIMEjava.sql.Time, LocalTime注意：只有最新的JDBC驱动才支持LocalDate和LocalTime。 练习使用JDBC查询数据库 小结JDBC接口的Connection代表一个JDBC连接； 使用JDBC查询时，总是使用PreparedStatement进行查询而不是Statement； 查询结果总是ResultSet，即使使用聚合查询也不例外。 |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |